package edu.chinasoft.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import edu.chinasoft.pojo.City;
import edu.chinasoft.pojo.Province;
import edu.chinasoft.util.JDBCTool;

public class ProvinceDaoImp implements ProvinceDao {
	private Logger logger = Logger.getLogger(ProvinceDaoImp.class);

	/* (non-Javadoc)
	 * @see edu.chinasoft.dao.ProvinceDao#selectProviderCity(edu.chinasoft.pojo.Province)
	 */
	@Override
	public Province selectProviderCity(Province province) {
		logger.debug("ProvinceDaoImp>>>selectProviderCity  start ……");

		// 查找分页里的记录
		Connection connection = JDBCTool.getConnection();
		String sql = "SELECT  t_province.`province_name` ,  t_city.id cityid,city_name FROM t_city,t_province WHERE t_city.`province_id`=t_province.`id`  AND t_province.id=?;";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		List<City> citys = new ArrayList<City>();
		try {
			prepareStatement = connection.prepareStatement(sql);
			prepareStatement.setInt(1, province.getId());
			resultSet = prepareStatement.executeQuery();

			while (resultSet.next()) {
				String provinceName = resultSet.getString("province_name");
				int cityId = resultSet.getInt("cityid");
				String cityName = resultSet.getString("city_name");
				province.setProvinceName(provinceName);
				City city = new City(cityId, cityName);
				citys.add(city);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}

		province.setCitys(citys);

		logger.debug("ProvinceDaoImp>>>selectProviderCity  end ……");
		return province;
	}
	@Override
	public List<Province> selectProvider() {
		logger.debug("ProvinceDaoImp>>>selectProvider  start ……");

		// 查找分页里的记录
		Connection connection = JDBCTool.getConnection();
		String sql = "SELECT t_province.`id`,t_province.`province_name` FROM t_province;";
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;
		List<Province> result = new ArrayList<Province>();
		try {
			prepareStatement = connection.prepareStatement(sql);
			resultSet = prepareStatement.executeQuery();

			while (resultSet.next()) {
				String provinceName = resultSet.getString("province_name");
				int id = resultSet.getInt("id");
				Province province = new Province(id, provinceName);
				result.add(province);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCTool.closeJdbc(resultSet, prepareStatement, connection);
		}

		logger.debug("ProvinceDaoImp>>>selectProvider  end ……");
		return result;
	}
}
